﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Data;
using PIM.Utilities.DataAccess;
using Nautilus;
using System.IO;
using System.Text;
using PIM.Common;

namespace PIM.API
{
    public static class Extensions
    {
        public static string QuotedValue(this string value)
        {
            return value.Replace("'", "''").Replace("\"", "\"\"");
        }
    }

    public class DataAccess : IDisposable
    {
        #region IDisposable implementation
        private PIMManager dbmanager = null;
        private bool disposed;
        public DataAccess(string connectionString)
        {
            dbmanager = new PIMManager(connectionString);
            dbmanager.Open();
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    if (dbmanager.ConnectionStatus == ConnectionState.Open)
                        dbmanager.Close();
                }
                disposed = true;
            }
        }

        // Destructor
        ~DataAccess()
        {
            Dispose(false);
        }
        #endregion IDisposable implementation

        #region WCF
        public List<RecipeUser> GetRecipeUser(string UserId)
        {
            List<RecipeUser> lstRecipt = new List<RecipeUser>();
            Hashtable inputHtable = new Hashtable();
            inputHtable.Add("RecipeId", 0);
            inputHtable.Add("UserId", UserId);
            Hashtable outPutHtable = new Hashtable();
            object obj = new object();
            DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeUser", inputHtable, ref outPutHtable, ref obj);
            if (_dt != null && _dt.Rows.Count > 0)
            {
                foreach (DataRow dr in _dt.Rows)
                {
                    RecipeUser recipe = new RecipeUser();
                    recipe.CookTime = Convert.ToInt32(dr["CookTime"].ToString());
                    recipe.Description = dr["Description"].ToString();
                    recipe.PrepTime = Convert.ToInt32(dr["PrepTime"].ToString());
                    recipe.RecDirection = dr["RecDirection"].ToString();
                    recipe.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                    recipe.RecipeName = dr["RecipeName"].ToString();
                    recipe.RecipeRating = Convert.ToDouble(dr["RecipeRating"].ToString());
                    recipe.Serving = Convert.ToInt32(dr["Serving"].ToString());
                    recipe.RecipeImage = dr["Image"].ToString();
                    recipe.UserName = dr["UserName"].ToString();
                    recipe.UserID = Convert.ToInt32(dr["Userid"].ToString());
                    lstRecipt.Add(recipe);
                }
            }

            return lstRecipt;
        }

        public List<RecipeUser> GetRecipeUserByRecipeId(string UserId, string RecipeId)
        {
            List<RecipeUser> lstRecipt = new List<RecipeUser>();
            Hashtable inputHtable = new Hashtable();
            inputHtable.Add("RecipeId", RecipeId);
            inputHtable.Add("UserId", UserId);
            Hashtable outPutHtable = new Hashtable();
            object obj = new object();
            DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeUser", inputHtable, ref outPutHtable, ref obj);
            if (_dt != null && _dt.Rows.Count > 0)
            {
                foreach (DataRow dr in _dt.Rows)
                {
                    RecipeUser recipe = new RecipeUser();
                    recipe.CookTime = Convert.ToInt32(dr["CookTime"].ToString());
                    recipe.Description = dr["Description"].ToString();
                    recipe.PrepTime = Convert.ToInt32(dr["PrepTime"].ToString());
                    recipe.RecDirection = dr["RecDirection"].ToString();
                    recipe.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                    recipe.RecipeName = dr["RecipeName"].ToString();
                    recipe.RecipeRating = Convert.ToDouble(dr["RecipeRating"].ToString());
                    recipe.Serving = Convert.ToInt32(dr["Serving"].ToString());
                    recipe.RecipeImage = dr["Image"].ToString();
                    recipe.UserName = dr["UserName"].ToString();
                    recipe.UserID = Convert.ToInt32(dr["Userid"].ToString());
                    lstRecipt.Add(recipe);
                }
            }

            return lstRecipt;
        }

        #endregion

        public bool UpdateRecipe(int recipeId, string recipeName,int userId, int serving, int prepTime, int cookTime, string photo ,
                                string description, double recipeRating, string recDirection, string recipeIngredient, bool isShared)
        {
            Hashtable inputHash = new Hashtable();
            try
            {
                inputHash.Add("RecipeName", recipeName);
                inputHash.Add("Serving", serving);
                inputHash.Add("UserID", userId);
                inputHash.Add("Preptime", prepTime);
                inputHash.Add("CookTime", cookTime);
                inputHash.Add("Photo", photo);
                inputHash.Add("Description", description);
                inputHash.Add("RecipeRating", recipeRating);
                inputHash.Add("RecDirection", recDirection);
                inputHash.Add("RecipeId", recipeId);
                inputHash.Add("RecipeIngredient", recipeIngredient);
                inputHash.Add("IsShared", isShared);
                int result = dbmanager.ExecuteStoreProcedure("sp_PIM_UpdateRecipeUser",inputHash);
                if (result >0)
                {
                    return true;
                }
            }
            catch (Exception)
            {

            }


            return false;
        }

        public int InsertRecipe(int userId, int createUserId, int parentUserId, string recipeName, int serving, int prepTime, 
            int cookTime, string photo, string description, double recipeRating, string recDirection, string recipeIngredient, bool isShared)
        {
            Hashtable inputHash = new Hashtable();
            Hashtable output = new Hashtable();
            object result = -1;
            try
            {
                inputHash.Add("UserId", userId);
                inputHash.Add("CreateUserId", createUserId);
                inputHash.Add("ParentUserId", parentUserId);
                inputHash.Add("RecipeName", recipeName);
                inputHash.Add("Serving", serving);
                inputHash.Add("Preptime", prepTime);
                inputHash.Add("CookTime", cookTime);
                inputHash.Add("Photo", photo);
                inputHash.Add("Description", description);
                inputHash.Add("RecipeRating", recipeRating);
                inputHash.Add("RecDirection", recDirection);
                inputHash.Add("RecipeIngredient", recipeIngredient);
                inputHash.Add("IsShared", isShared);
                output.Add("RecipeId", -1);
                dbmanager.GetData("sp_PIM_InsertRecipeUser", inputHash, ref output,ref result);
                if (Int32.Parse(output["RecipeId"].ToString()) > 0)
                {
                    return Int32.Parse(output["RecipeId"].ToString());
                }
                else
                {
                    return -1;
                }
            }
            catch (Exception)
            {
                return -1;
            }

        }

        public bool DeleteRecipe(int recipeID)
        {
            string sql = string.Format("Delete from RecipeUser where RecipeId={0}", recipeID);
            try
            {
                if (DeleteAllRecipeIngredient(recipeID))
                {
                    DeleteRecipeFromMealPlan(recipeID);
                    if (dbmanager.Execute(sql) == 1)
                        return true;
                }
            }
            catch (Exception)
            {

            }
            return false;
        }

        public bool DeleteRecipeFromMealPlan(int recipeID)
        {
            string sql = string.Format("Delete from MealPlanDetail where RecipeId={0}", recipeID);
            try
            {
                if (dbmanager.Execute(sql) == 1)
                    return true;
            }
            catch (Exception)
            {

            }
            return false;
        }
        public bool DeleteMealPlanDetail(int mealPlanId)
        {
            string sql = string.Format("Delete from MealPlanDetail where MealPlanID={0}", mealPlanId);
            try
            {
                if (dbmanager.Execute(sql) >= 0)
                    return true;
            }
            catch (Exception)
            {

            }
            return false;
        }
        public bool DeleteReportExercises(int userid, int ID)
        {
            string sql = string.Format("DELETE Rpt_MyExercises WHERE UserID={0} AND ID={1}", userid, ID);
            try
            {
                if (dbmanager.Execute(sql) >= 0)
                    return true;
            }
            catch (Exception)
            {

            }
            return false;
        }
        public bool DelteReportFoodDrink(int userid, int ID)
        {
            string sql = string.Format("DELETE Rpt_Today WHERE UserID={0} AND ID={1}", userid, ID);
            try
            {
                if (dbmanager.Execute(sql) >= 0)
                    return true;
            }
            catch (Exception)
            {

            }
            return false;
        }
        public bool DeleteReportWeights(int userid, int ID)
        {
            string sql = string.Format("DELETE Rpt_MyWeights WHERE UserID={0} AND ID={1}", userid, ID);
            try
            {
                if (dbmanager.Execute(sql) >= 0)
                    return true;
            }
            catch (Exception)
            {

            }
            return false;
        }
        public bool DeleteReports(int userid, int ID)
        {
            string sql = string.Format("DELETE Rpt_Today WHERE UserID={0} AND ID={1}", userid, ID);
            try
            {
                if (dbmanager.Execute(sql) >= 0)
                    return true;
            }
            catch (Exception)
            {

            }
            return false;
        }
        public bool DeleteIngredientListUser(int userid)
        {
            string sql = string.Format("Delete from IngredientListUser where UserId={0}", userid);
            try
            {
                if (dbmanager.Execute(sql) >= 0)
                    return true;
            }
            catch (Exception)
            {

            }
            return false;
        }

        public bool UpdateRecipeIngredientUser(int recipeIngredientUserID, string ingredientName, int quantity, int units, string directions)
        {
            string sql = string.Format("Update RecipeIngredientUser set IngredientName='{0}', Quantity ={1} , Units ={2}, Directions='{3}'"
            + " where RecipeIngredientUserID={4}", ingredientName, quantity, units, directions, recipeIngredientUserID);
            try
            {
                if (dbmanager.Execute(sql) == 1)
                    return true;
            }
            catch (Exception)
            {

            }
            return false;
        }

        public bool DeleteRecipeIngredientUser(int recipeIngredientUserID)
        {
            string sql = string.Format("Delete from RecipeIngredientUser where RecipeIngredientUserID={0}", recipeIngredientUserID);
            try
            {
                if (dbmanager.Execute(sql) == 1)
                    return true;
            }
            catch (Exception)
            {

            }
            return false;
        }

        public bool DeleteAllRecipeIngredient(int recipeID)
        {
            string sql = string.Format("Delete from RecipeIngredientUser where recipeID={0}", recipeID);
            try
            {
                if (dbmanager.Execute(sql) >= 0)
                    return true;
            }
            catch (Exception)
            {

            }
            return false;
        }

        public bool InsertRecipeIngredientUser(int recipeID, string ingredientIngredientName, int quantity, int units, int direction)
        {
            Hashtable inputHash = new Hashtable();
            try
            {
                inputHash.Add("RecipeId", recipeID);
                inputHash.Add("IngredientName", ingredientIngredientName);
                inputHash.Add("Quantity", quantity);
                inputHash.Add("Units", units);
                inputHash.Add("Directions", direction);
                int result = dbmanager.ExecuteStoreProcedure("sp_PIM_InsertRecipeIngredientUser", inputHash);
                if (result == 1)
                {
                    return true;
                }
            }
            catch (Exception)
            {

            }
            return false;
        }

        public RecipeUser getRecipeUser(int recipeID)
        {
            RecipeUser recipeUser = new RecipeUser();
            string sql = string.Format("SELECT * FROM RECIPEUSER WHERE RECIPEID={0}", recipeID);
            try
            {
                DataRow dr = dbmanager.GetDataRow(sql);
                if(dr != null)
                {
                    recipeUser.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                    recipeUser.RecipeName = dr["RecipeName"].ToString();
                    recipeUser.RecipeRating = Convert.ToDouble(dr["RecipeRating"].ToString());
                    recipeUser.PrepTime = Convert.ToInt32(dr["PrepTime"].ToString());
                    recipeUser.Serving = Convert.ToInt32(dr["Serving"].ToString());
                    recipeUser.CookTime = Convert.ToInt32(dr["CookTime"].ToString());
                    recipeUser.RecDirection = dr["RecDirection"].ToString();
                    recipeUser.Description = dr["Description"].ToString();
                    return recipeUser; 
                }
                      
            }
            catch (Exception)
            {

            }
            return null;
       
        }
        

        public List<RecipeIngredientUser> getRecipeIngredientUser(int recipeID)
        {
            string sql = string.Format("SELECT * FROM RecipeIngredientUser WHERE RECIPEID="+ recipeID.ToString());
            List<RecipeIngredientUser> list = new List<RecipeIngredientUser>();
            try
            {
                DataTable dt = new DataTable();
                dt = dbmanager.GetData(sql);
                if (dt != null && dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr = dt.Rows[i];
                        RecipeIngredientUser obj = new RecipeIngredientUser();
                        obj.RecipeIngredientUserID = Convert.ToInt32(dr["RecipeIngredientUserID"].ToString());
                        obj.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                        obj.Quantity = Convert.ToInt32(dr["Quantity"].ToString());
                        obj.Units = Convert.ToInt32(dr["Units"].ToString());
                        obj.IngredientName = dr["IngredientName"].ToString();
                        obj.Directions = dr["Directions"].ToString();
                        list.Add(obj);
                    }
                    return list;
                }
            }
            catch (Exception)
            {

            }
            return null;

        }

        public RecipeIngredientUser getIngredientUserById(int IngredientUserId)
        {
            string sql = string.Format("SELECT * FROM RecipeIngredientUser WHERE RecipeIngredientUserID=" + IngredientUserId.ToString());
            try
            {
                DataTable dt = new DataTable();
                dt = dbmanager.GetData(sql);
                RecipeIngredientUser obj = new RecipeIngredientUser();
                if (dt != null && dt.Rows.Count > 0)
                {
                    obj.RecipeIngredientUserID = Convert.ToInt32(dt.Rows[0]["RecipeIngredientUserID"].ToString());
                    obj.RecipeID = Convert.ToInt32(dt.Rows[0]["RecipeID"].ToString());
                    obj.Quantity = Convert.ToInt32(dt.Rows[0]["Quantity"].ToString());
                    obj.Units = Convert.ToInt32(dt.Rows[0]["Units"].ToString());
                    obj.IngredientName = dt.Rows[0]["IngredientName"].ToString();
                    obj.Directions = dt.Rows[0]["Directions"].ToString();
                }
                return obj;
            }
            catch (Exception)
            {

            }
            return null;

        }

        public bool DeleteIngredientUserByIngredientID(string _IngredientID)
        {
            string sql = string.Format("Delete from RecipeIngredientUser where RecipeIngredientUserID={0}", _IngredientID);
            try
            {
                if (dbmanager.Execute(sql) > 0)
                    return true;
            }
            catch (Exception)
            {

            }
            return false;
        }

        public List<RecipeUser> SearchRecipes(Predicates predicates)
        {
            List<RecipeUser> recipeUser = new List<RecipeUser>();
            try
            {
                StringBuilder where = new StringBuilder();
                foreach (Predicate predicate in predicates)
                    where.AppendFormat("{0}{1}", where.Length > 0 ? " AND " : string.Empty, predicate.ToString());
               // where.AppendFormat("{0}VISITORSTATUS <> 'Checked Out'", where.Length > 0 ? " AND " : string.Empty);

                DataTable dt = dbmanager.GetData(string.Format("SELECT * FROM RecipeUser WHERE {0}", where));
                if (dt != null)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        recipeUser.Add(ConstructVisitor(dr));
                    }
                }
            }
            catch (Exception ex)
            {
                //Common.RaiseException(dbmanager, ex, "", "SearchVisitors");
            }

            return recipeUser;
        }

        private RecipeUser ConstructVisitor(DataRow dr)
        {
            return new RecipeUser()
            {
                 RecipeID = Convert.ToInt32(dr["RecipeID"].ToString()),
                 RecipeName = dr["RecipeName"].ToString(),
                 RecipeRating = Convert.ToDouble(dr["RecipeRating"].ToString()),
                 PrepTime = Convert.ToInt32(dr["PrepTime"].ToString()),
                 Serving = Convert.ToInt32(dr["Serving"].ToString()),
                 CookTime = Convert.ToInt32(dr["CookTime"].ToString()),
                 RecDirection = dr["RecDirection"].ToString(),
                 Description = dr["Description"].ToString(),
                
            };
        }
        public bool DeletMealPlan(int mealPlanID)
        {
            string sql = string.Format("Delete from MealPlan where mealPlanID={0}", mealPlanID);
            try
            {
                DeleteMealPlanDetail(mealPlanID);
                if (dbmanager.Execute(sql) > 0)
                    return true;
            }
            catch (Exception)
            {

            }
            return false;
        }

        public bool InsertMyRecipe(int _recipeid, int _userId, int _oldUser)
        {
            Hashtable inputHash = new Hashtable();
            try
            {
                inputHash.Add("UserID", _userId);
                inputHash.Add("RecipeId", _recipeid);
                inputHash.Add("OldUserId", _oldUser);
                int result = dbmanager.ExecuteStoreProcedure("sp_PIM_InsertMyRecipe", inputHash);
                if (result == 1)
                {
                    return true;
                }
            }
            catch (Exception)
            {

            }
            return false;
        }

        public bool InsertMyMealPlan(int _mealPlanid, int _userId, int _oldUser)
        {
            Hashtable inputHash = new Hashtable();
            try
            {
                inputHash.Add("UserID", _userId);
                inputHash.Add("PlanMealId", _mealPlanid);
                inputHash.Add("OldUserId", _oldUser);
                int result = dbmanager.ExecuteStoreProcedure("sp_PIM_InsertMyPlanMeal", inputHash);
                if (result == 1)
                {
                    return true;
                }
            }
            catch (Exception)
            {

            }
            return false;
        }

        public string CheckMealPlanName(string MealPlanName)
        {
            string sql = "select * from MealPlan where MealPlanName='" + MealPlanName + "'";

            DataTable dt= dbmanager.GetData(sql);
            if (dt != null && dt.Rows.Count > 0)
                return "1";
            else
                return "0";
        }

        public bool CreateMealPlan(string UserID, string MealPlanName, string recipeidlist)
        {
            Hashtable inputHash = new Hashtable();
            try
            {
                inputHash.Add("UserID", UserID);
                inputHash.Add("MealPlanName", MealPlanName);
                inputHash.Add("recipeidlist", recipeidlist);
                int result = dbmanager.ExecuteStoreProcedure("sp_PIM_CreateMealPlan", inputHash);
                if (result == 1)
                {
                    return true;
                }
            }
            catch (Exception)
            {

            }
            return false;
        }

        public bool CreateNutrition(string UserID, string recipeidlist)
        {
            Hashtable inputHash = new Hashtable();
            try
            {
                inputHash.Add("userid", UserID);
                inputHash.Add("recipeList", recipeidlist);
                int result = dbmanager.ExecuteStoreProcedure("sp_PIM_InsertNutrition", inputHash);
                if (result == 1)
                {
                    return true;
                }
            }
            catch (Exception)
            {

            }
            return false;
        }

        public bool InsertMealPlanDetail(int _mealPlanID, int _mealTypeID, int _recipeID, int _groceryItemID, int _planTypeID, int _noOfServings,
            DateTime _dateCreated, string _dayOfWeek, string _userId)
        {
            Hashtable inputHash = new Hashtable();
            Hashtable output = new Hashtable();
            object result = -1;
            try
            {
                inputHash.Add("MealPlanID", _mealPlanID);
                inputHash.Add("MealTypeID", _mealTypeID);
                inputHash.Add("RecipeID", _recipeID);
                inputHash.Add("GroceryItemID", _groceryItemID);
                inputHash.Add("PlanTypeID", _planTypeID);
                inputHash.Add("NoOfServings", _noOfServings);
                inputHash.Add("DateCreated", _dateCreated);
                inputHash.Add("DayOfWeek", _dayOfWeek);
                inputHash.Add("UserId", _userId);
                output.Add("Rows", -1);
                dbmanager.GetData("sp_PIM_InsertMealPlanDetail", inputHash, ref output, ref result);
                if (Int32.Parse(output["Rows"].ToString()) > 0)
                {
                    return true;
                }
            }
            catch (Exception)
            {

            }


            return false;
        }

        public int InsertMealPlan(int _userId, int _createdUserId, int _parentUserId, string _mealPlanName, bool _isPublished, bool _canCopy, double _mealPlanRating,
            int _avgCalPerDay, int _estCostPerDay, string _mealPlanPhoto, int _noOfReview,string _description,string _basicInfo)
        {
            Hashtable inputHash = new Hashtable();
            Hashtable output = new Hashtable();
            object result = -1;
            int MealPlanId = -1;
            try
            {
                inputHash.Add("UserID", _userId);
                inputHash.Add("CreatedUserID", _createdUserId);
                inputHash.Add("ParentUserID", _parentUserId);
                inputHash.Add("MealPlanName", _mealPlanName);
                inputHash.Add("isShared", _isPublished);
                inputHash.Add("canCopy", _canCopy);
                inputHash.Add("NoOfReview", _noOfReview);
                inputHash.Add("MealPlanRating", _mealPlanRating);
                inputHash.Add("AvgCalPerDay", _avgCalPerDay);
                inputHash.Add("EstCostPerDay", _estCostPerDay);
                inputHash.Add("MealPlanPhoto", _mealPlanPhoto);
                inputHash.Add("Description",  _description);
                inputHash.Add("BasicInfo", _basicInfo);
                
                output.Add("MealPlanId", -1);
                dbmanager.GetData("sp_PIM_InsertPlanItMeal", inputHash, ref output, ref result);
                MealPlanId = Int32.Parse(output["MealPlanId"].ToString());
            }
            catch (Exception)
            {
                MealPlanId = -1;
            }


            return MealPlanId;
        }

        public bool UpdateMealPlan(int MealPlanId, int UserID, int CreatedUserID, int ParentUserID, string MealPlanName, bool isPublished, bool canCopy,
        int NoOfReview, double MealPlanRating, int AvgCalPerDay, int EstCostPerDay, string MealPlanPhoto, string Description, string BasicInfo)
        {
            Hashtable inputHash = new Hashtable();
            try
            {
                inputHash.Add("MealPlanId", MealPlanId);
                inputHash.Add("UserID", UserID);
                inputHash.Add("CreatedUserID", CreatedUserID);
                inputHash.Add("ParentUserID", ParentUserID);
                inputHash.Add("MealPlanName", MealPlanName);
                inputHash.Add("isShared", isPublished);
                inputHash.Add("canCopy", canCopy);
                inputHash.Add("NoOfReview", NoOfReview);
                inputHash.Add("MealPlanRating", MealPlanRating);
                inputHash.Add("AvgCalPerDay", AvgCalPerDay);
                inputHash.Add("EstCostPerDay", EstCostPerDay);
                inputHash.Add("MealPlanPhoto", MealPlanPhoto);
                inputHash.Add("Description", Description);
                inputHash.Add("BasicInfo", BasicInfo);
                int result = dbmanager.ExecuteStoreProcedure("sp_PIM_UpdatePlanItMeal", inputHash);
                if (result > 0)
                {
                    return true;
                }
            }
            catch (Exception)
            {

            }
            return false;
        }

        public bool InsertIngredient(int _userId, string _IngredientListName)
        {
            Hashtable inputHash = new Hashtable();
            Hashtable output = new Hashtable();
            object result = -1;
            int row = -1;
            try
            {
                inputHash.Add("UserID", _userId);
                inputHash.Add("IngredientListName", _IngredientListName);
                output.Add("rows", -1);
                dbmanager.GetData("sp_PIM_InsertIngredientListUser", inputHash, ref output, ref result);
                row = Int32.Parse(output["rows"].ToString());
            }
            catch (Exception)
            {
                row = -1;
            }

            if (row > 0) return true;
            else return false;
        }

        public string getIngredientStringByRecipeList(string recipeidlist)
        {
            string ingredientlist = "";
            Hashtable htinput = new Hashtable();
            Hashtable htoutput = new Hashtable();
            object obj = new object();
            htinput.Add("RecipeID", recipeidlist);
            DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeIngredientUserForListID", htinput,ref htoutput, ref obj);
            if (_dt!=null && _dt.Rows.Count>0)
            {
                foreach (DataRow r in _dt.Rows)
                {
                    string ingre="("+r["RecipeName"].ToString() + "_" + r["RecipeID"].ToString() + "^" + r["Serving"].ToString() + "^" + r["IngredientName"].ToString();
                    //if (string.IsNullOrEmpty(ingredientlist))
                    //    ingredientlist = ingre;
                    //else
                    //    ingredientlist += ingre + "]";
                    ingredientlist += ingre + "]";
                }
            }
            return ingredientlist;
        }

        public int CreateNewUser(string UserName, string Password)
        {
            Hashtable inputHash = new Hashtable();
            Hashtable output = new Hashtable();
            object result = -1;
            int row = -1;
            try
            {
                inputHash.Add("UserName", UserName);
                inputHash.Add("Password", Password);
                output.Add("rows", -1);
                dbmanager.GetData("sp_PIM_CreateNewUser", inputHash, ref output, ref result);
                row = Int32.Parse(output["rows"].ToString());
            }
            catch (Exception)
            {
                row = -1;
            }

            return row;
        }

        public bool ChangPassword(string UserId, string Password)
        {
            Hashtable inputHash = new Hashtable();
            Hashtable output = new Hashtable();
            object result = -1;
            int row = -1;
            try
            {
                inputHash.Add("UserId", UserId);
                inputHash.Add("Password", Password);
                output.Add("rows", -1);
                dbmanager.GetData("sp_PIM_ChangePassword", inputHash, ref output, ref result);
                row = Int32.Parse(output["rows"].ToString());
            }
            catch (Exception)
            {
                row = -1;
            }

            if (row > 0) return true;
            else return false;
        }

        public bool CheckRecipeOfMealPlan(int recipeID)
        {
            string sql = string.Format("Select top 1 * from MealPlanDetail where RecipeID = {0}", recipeID);
            try
            {
                DataTable dt = dbmanager.GetData(sql);
                if (dt != null && dt.Rows.Count > 0)
                    return true;
            }
            catch (Exception)
            {

            }
            return false;
        }

        public bool Rating(int objectTypeID, int objectID, int rating)
        {
            Hashtable inputHash = new Hashtable();

            try
            {
                inputHash.Add("ObjectTypeID", objectTypeID);
                inputHash.Add("ObjectID", objectID);
                inputHash.Add("rating", rating);
                int result = dbmanager.ExecuteStoreProcedure("sp_PIM_Rating", inputHash);
                if (result > 0)
                {
                    return true;
                }
            }
            catch (Exception)
            {
                
            }
            return false;
        }

        public bool InsertFeedback(string _email, string _title, string _comment)
        {
            Hashtable inputHash = new Hashtable();

            try
            {
                inputHash.Add("emailFeedback", _email);
                inputHash.Add("titleFeedback", _title);
                inputHash.Add("comment", _comment);
                int result = dbmanager.ExecuteStoreProcedure("sp_PIM_InsertFeedback", inputHash);
                if (result > 0)
                {
                    return true;
                }
            }
            catch (Exception)
            {

            }
            return false;
        }

        public int InsertReportFoodDrink(int UserID, string CalorieName, float Calorie, float Fat, float Carb, float Protein, int Quantity, string Unit, string Date, int RecipeID)
        {
            Hashtable inputHash = new Hashtable();
            Hashtable output = new Hashtable();
            object result = -1;
            int row = -1;
            try
            {
                inputHash.Add("UserID", UserID);
                inputHash.Add("CalorieName", CalorieName);
                inputHash.Add("Calorie", Calorie);
                inputHash.Add("Fat", Fat);
                inputHash.Add("Carb", Carb);
                inputHash.Add("Protein", Protein);
                inputHash.Add("Quantity", Quantity);
                inputHash.Add("Unit", Unit);
                inputHash.Add("Date", Date);
                inputHash.Add("RecipeID", RecipeID);
                output.Add("rows", -1);
                dbmanager.GetData("sp_Rpt_InsertReportFoodDrink", inputHash, ref output, ref result);
                row = Int32.Parse(output["rows"].ToString());
            }
            catch (Exception)
            {
                row = -1;
            }

            return row;
        }
        public int InsertReportGoals(int UserID, string Date, float Calorie, float Fat, float Carb, float Protein, float Weight, float Exercise)
        {
            Hashtable inputHash = new Hashtable();
            Hashtable output = new Hashtable();
            object result = -1;
            int row = -1;
            try
            {
                inputHash.Add("UserID", UserID);
                inputHash.Add("Date", Date);
                inputHash.Add("Calorie", Calorie);
                inputHash.Add("Fat", Fat);
                inputHash.Add("Carb", Carb);
                inputHash.Add("Protein", Protein);
                inputHash.Add("Weight", Weight);
                inputHash.Add("Exercise", Exercise);
                output.Add("rows", -1);
                dbmanager.GetData("sp_Rpt_InsertReportGoals", inputHash, ref output, ref result);
                row = Int32.Parse(output["rows"].ToString());
            }
            catch (Exception)
            {
                row = -1;
            }
            return row;
        }
        public int EditReportGoals(int UserID, string Date, float Calorie, float Fat, float Carb, float Protein, float Weight, float Exercise)
        {
            Hashtable inputHash = new Hashtable();
            Hashtable output = new Hashtable();
            object result = -1;
            int row = -1;
            try
            {
                inputHash.Add("UserID", UserID);
                inputHash.Add("Date", Date);
                inputHash.Add("Calorie", Calorie);
                inputHash.Add("Fat", Fat);
                inputHash.Add("Carb", Carb);
                inputHash.Add("Protein", Protein);
                inputHash.Add("Weight", Weight);
                inputHash.Add("Exercise", Exercise);
                output.Add("rows", -1);
                dbmanager.GetData("sp_Rpt_EditReportGoals", inputHash, ref output, ref result);
                row = Int32.Parse(output["rows"].ToString());
            }
            catch (Exception)
            {
                row = -1;
            }
            return row;
        }
        
        public int InserReportExercises(int UserID, string Date, string ExerciseName, float Cal)
        {
            Hashtable inputHash = new Hashtable();
            Hashtable output = new Hashtable();
            object result = -1;
            int row = -1;
            try
            {
                inputHash.Add("UserID", UserID);
                inputHash.Add("Date", Date);
                inputHash.Add("ExerciseName", ExerciseName);
                inputHash.Add("Cal", Cal);
                output.Add("rows", -1);
                dbmanager.GetData("sp_Rpt_InserReportExercises", inputHash, ref output, ref result);
                row = Int32.Parse(output["rows"].ToString());
            }
            catch (Exception)
            {
                row = -1;
            }

            return row;
        }
        public int InserReportWeights(int UserID, string Date, float Weight)
        {
            Hashtable inputHash = new Hashtable();
            Hashtable output = new Hashtable();
            object result = -1;
            int row = -1;
            try
            {
                inputHash.Add("UserID", UserID);
                inputHash.Add("Date", Date);
                inputHash.Add("Weight", Weight);
                output.Add("rows", -1);
                dbmanager.GetData("sp_Rpt_InserReportWeights", inputHash, ref output, ref result);
                row = Int32.Parse(output["rows"].ToString());
            }
            catch (Exception)
            {
                row = -1;
            }

            return row;
        }
        public List<Nutrition> getNutritionByIngerdient(string ingredient)
        {
            //string sql = string.Format("SELECT * FROM RecipeIngredientUser WHERE RECIPEID=" + recipeID.ToString());
            List<Nutrition> lst = new List<Nutrition>();
            try
            {
                DataTable dt = new DataTable();
                Hashtable htInput = new Hashtable();
                Hashtable htOutput = new Hashtable();
                object obj = new object();
                htInput.Add("Str_SearchStr", ingredient);
                dt = dbmanager.GetData("sp_PIM_GetNutritionByIngredient", htInput, ref htOutput, ref obj);
                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        Nutrition nutri = new Nutrition();
                        if (dr["Carbohydrt_(g)"]!=null)
                            nutri.Carbohydrt =Convert.ToDecimal(dr["Carbohydrt_(g)"]);
                        if (dr["Energ_Kcal"] != null)
                            nutri.Energ_Kcal = Convert.ToInt32(dr["Energ_Kcal"]);
                        if (dr["Lipid_Tot_(g)"] != null)
                            nutri.Lipid_Tot = Convert.ToDecimal(dr["Lipid_Tot_(g)"]);
                        if (dr["NDB_No"] != null)
                            nutri.NDP_No = Convert.ToInt32(dr["NDB_No"]);
                        nutri.Long_Desc = dr["Long_Desc"].ToString();
                        if (dr["Protein_(g)"] != null)
                            nutri.Protein = Convert.ToDecimal(dr["Protein_(g)"]);
                        lst.Add(nutri);
                    }
                    return lst;
                }
            }
            catch (Exception)
            {

            }
            return null;
        }
    }
}
